{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import os\n",
    "import matplotlib.font_manager as fm\n",
    "import pandas as pd\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.model_selection import train_test_split\n",
    "import pandas as pd\n",
    "from statsmodels.tsa.arima.model import ARIMA\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "from docx import Document"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 指定中文字体\n",
    "matplotlib.rcParams['font.sans-serif'] = ['SimHei']\n",
    "\n",
    "# 读取预处理过的数据\n",
    "original = pd.read_excel('./第二问预处理.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 删除指定列\n",
    "original.drop(columns=['扫码销售时间', '辅助列', '销售单价(元/千克)', '销售类型', '是否打折销售', '分类编码', '批发价格(元/千克)', '损耗率(%)'], inplace=True)\n",
    "\n",
    "# 按照时间顺序排序\n",
    "original.sort_values('销售日期', inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分别求每天的成本加成定价-第一步 加总\n",
    "original['成本加成定价*单次销量'] = original['成本加成定价(元/千克)'] * original['销量(千克)'] \n",
    "\n",
    "# 按照分类名称进行分组，计算每个单品的销售量\n",
    "original = original.groupby(['销售日期','单品编码']).agg({'销量(千克)': 'sum', '成本加成定价*单次销量': 'sum'})\n",
    "\n",
    "\n",
    "# 分布求每天的成本加成定价-第二步 除以当天该品种总销量\n",
    "original['成本加成定价'] = original['成本加成定价*单次销量'] / original['销量(千克)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 删除多余项\n",
    "original=original.drop(columns=['成本加成定价*单次销量'], inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用pivot_table将时间作为列，单品编码作为行，并将每个单品编码在该时间下的成本加成定价作为值\n",
    "pivot_table = original.pivot_table(index='销售日期', columns='单品编码', values='成本加成定价')\n",
    "# 将pivot_table中的NaN值填充为0\n",
    "pivot_table.fillna(0, inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>单品编码</th>\n",
       "      <th>102900005115168</th>\n",
       "      <th>102900005115199</th>\n",
       "      <th>102900005115250</th>\n",
       "      <th>102900005115625</th>\n",
       "      <th>102900005115748</th>\n",
       "      <th>102900005115762</th>\n",
       "      <th>102900005115779</th>\n",
       "      <th>102900005115786</th>\n",
       "      <th>102900005115793</th>\n",
       "      <th>102900005115816</th>\n",
       "      <th>...</th>\n",
       "      <th>106956146480203</th>\n",
       "      <th>106957634300010</th>\n",
       "      <th>106957634300058</th>\n",
       "      <th>106958851400125</th>\n",
       "      <th>106971533450003</th>\n",
       "      <th>106971533455008</th>\n",
       "      <th>106971563780002</th>\n",
       "      <th>106972776821582</th>\n",
       "      <th>106973223300667</th>\n",
       "      <th>106973990980123</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>销售日期</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-07-01</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.881905</td>\n",
       "      <td>9.209204</td>\n",
       "      <td>6.502984</td>\n",
       "      <td>16.758918</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-02</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.893270</td>\n",
       "      <td>8.713585</td>\n",
       "      <td>6.501408</td>\n",
       "      <td>16.754811</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-03</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.811443</td>\n",
       "      <td>8.829528</td>\n",
       "      <td>6.227073</td>\n",
       "      <td>16.755633</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-04</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>9.038260</td>\n",
       "      <td>6.545557</td>\n",
       "      <td>16.752347</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-07-05</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.790987</td>\n",
       "      <td>9.035842</td>\n",
       "      <td>6.541278</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-26</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.888789</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.529597</td>\n",
       "      <td>10.236460</td>\n",
       "      <td>4.381574</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.400000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-27</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.888789</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.531870</td>\n",
       "      <td>10.232861</td>\n",
       "      <td>4.389458</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-28</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.888789</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.938689</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.754770</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-29</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.888789</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.938689</td>\n",
       "      <td>10.222065</td>\n",
       "      <td>3.740579</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.890909</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-06-30</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.888789</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.902322</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.739002</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.400000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1085 rows × 246 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "单品编码        102900005115168  102900005115199  102900005115250  \\\n",
       "销售日期                                                            \n",
       "2020-07-01              0.0              0.0         0.000000   \n",
       "2020-07-02              0.0              0.0         0.000000   \n",
       "2020-07-03              0.0              0.0         0.000000   \n",
       "2020-07-04              0.0              0.0         0.000000   \n",
       "2020-07-05              0.0              0.0         0.000000   \n",
       "...                     ...              ...              ...   \n",
       "2023-06-26              0.0              0.0        25.888789   \n",
       "2023-06-27              0.0              0.0        25.888789   \n",
       "2023-06-28              0.0              0.0        25.888789   \n",
       "2023-06-29              0.0              0.0        25.888789   \n",
       "2023-06-30              0.0              0.0        25.888789   \n",
       "\n",
       "单品编码        102900005115625  102900005115748  102900005115762  \\\n",
       "销售日期                                                            \n",
       "2020-07-01              0.0              0.0         6.881905   \n",
       "2020-07-02              0.0              0.0         6.893270   \n",
       "2020-07-03              0.0              0.0         6.811443   \n",
       "2020-07-04              0.0              0.0         0.000000   \n",
       "2020-07-05              0.0              0.0         6.790987   \n",
       "...                     ...              ...              ...   \n",
       "2023-06-26              0.0              0.0         4.529597   \n",
       "2023-06-27              0.0              0.0         4.531870   \n",
       "2023-06-28              0.0              0.0         3.938689   \n",
       "2023-06-29              0.0              0.0         3.938689   \n",
       "2023-06-30              0.0              0.0         3.902322   \n",
       "\n",
       "单品编码        102900005115779  102900005115786  102900005115793  \\\n",
       "销售日期                                                            \n",
       "2020-07-01         9.209204         6.502984        16.758918   \n",
       "2020-07-02         8.713585         6.501408        16.754811   \n",
       "2020-07-03         8.829528         6.227073        16.755633   \n",
       "2020-07-04         9.038260         6.545557        16.752347   \n",
       "2020-07-05         9.035842         6.541278         0.000000   \n",
       "...                     ...              ...              ...   \n",
       "2023-06-26        10.236460         4.381574         0.000000   \n",
       "2023-06-27        10.232861         4.389458         0.000000   \n",
       "2023-06-28         0.000000         3.754770         0.000000   \n",
       "2023-06-29        10.222065         3.740579         0.000000   \n",
       "2023-06-30         0.000000         3.739002         0.000000   \n",
       "\n",
       "单品编码        102900005115816  ...  106956146480203  106957634300010  \\\n",
       "销售日期                         ...                                     \n",
       "2020-07-01              0.0  ...              0.0              0.0   \n",
       "2020-07-02              0.0  ...              0.0              0.0   \n",
       "2020-07-03              0.0  ...              0.0              0.0   \n",
       "2020-07-04              0.0  ...              0.0              0.0   \n",
       "2020-07-05              0.0  ...              0.0              0.0   \n",
       "...                     ...  ...              ...              ...   \n",
       "2023-06-26              0.0  ...              0.0              0.0   \n",
       "2023-06-27              0.0  ...              0.0              0.0   \n",
       "2023-06-28              0.0  ...              0.0              0.0   \n",
       "2023-06-29              0.0  ...              0.0              0.0   \n",
       "2023-06-30              0.0  ...              0.0              0.0   \n",
       "\n",
       "单品编码        106957634300058  106958851400125  106971533450003  \\\n",
       "销售日期                                                            \n",
       "2020-07-01              0.0              0.0         0.000000   \n",
       "2020-07-02              0.0              0.0         0.000000   \n",
       "2020-07-03              0.0              0.0         0.000000   \n",
       "2020-07-04              0.0              0.0         0.000000   \n",
       "2020-07-05              0.0              0.0         0.000000   \n",
       "...                     ...              ...              ...   \n",
       "2023-06-26              0.0              0.0         2.400000   \n",
       "2023-06-27              0.0              0.0         3.000000   \n",
       "2023-06-28              0.0              0.0         3.000000   \n",
       "2023-06-29              0.0              0.0         2.890909   \n",
       "2023-06-30              0.0              0.0         2.400000   \n",
       "\n",
       "单品编码        106971533455008  106971563780002  106972776821582  \\\n",
       "销售日期                                                            \n",
       "2020-07-01              0.0              0.0              0.0   \n",
       "2020-07-02              0.0              0.0              0.0   \n",
       "2020-07-03              0.0              0.0              0.0   \n",
       "2020-07-04              0.0              0.0              0.0   \n",
       "2020-07-05              0.0              0.0              0.0   \n",
       "...                     ...              ...              ...   \n",
       "2023-06-26              0.0              0.0              0.0   \n",
       "2023-06-27              0.0              0.0              0.0   \n",
       "2023-06-28              0.0              0.0              0.0   \n",
       "2023-06-29              0.0              0.0              0.0   \n",
       "2023-06-30              0.0              0.0              0.0   \n",
       "\n",
       "单品编码        106973223300667  106973990980123  \n",
       "销售日期                                          \n",
       "2020-07-01              0.0              0.0  \n",
       "2020-07-02              0.0              0.0  \n",
       "2020-07-03              0.0              0.0  \n",
       "2020-07-04              0.0              0.0  \n",
       "2020-07-05              0.0              0.0  \n",
       "...                     ...              ...  \n",
       "2023-06-26              0.0              0.0  \n",
       "2023-06-27              0.0              0.0  \n",
       "2023-06-28              0.0              0.0  \n",
       "2023-06-29              0.0              0.0  \n",
       "2023-06-30              0.0              0.0  \n",
       "\n",
       "[1085 rows x 246 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivot_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导出到Excel\n",
    "pivot_table.to_excel('./results/第三问/各单品求加权成本加成定价.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
